package zy.dao.buy.supply.impl;

import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import zy.dao.BaseDaoImpl;
import zy.dao.buy.supply.SupplyDAO;
import zy.dto.buy.money.SupplyMoneyDetailsDto;
import zy.entity.buy.supply.T_Buy_Supply;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class SupplyDAOImpl extends BaseDaoImpl implements SupplyDAO {

	@Override
	public List<T_Buy_Supply> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object ar_upcode = param.get("ar_upcode");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,spi_man,spi_tel,spi_mobile,sp_rate,sp_buy_cycle,sp_settle_cycle,spi_earnest,spi_deposit,spi_addr,spi_remark,");
		sql.append(" sp_init_debt,IFNULL(sp_payable,0) AS sp_payable, IFNULL(sp_payabled,0) AS sp_payabled,IFNULL(sp_prepay,0) AS sp_prepay");
		sql.append(" FROM t_buy_supply supply ");
		sql.append(" INNER JOIN t_buy_supply_info supplyinfo");
		sql.append(" ON supply.sp_code = supplyinfo.spi_sp_code ");
		sql.append(" AND supply.companyid = supplyinfo.companyid ");
		sql.append(" WHERE 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" AND INSTR(supply.sp_name,:name)>0");
        }
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(supply.sp_code,:searchContent)>0 OR INSTR(supply.sp_name,:searchContent)>0)");
        }
        if(null != ar_upcode && !"".equals(ar_upcode)){
        	sql.append(" AND supply.sp_ar_code = :ar_upcode ");
        }
		sql.append(" AND supply.companyid=:companyid");
		sql.append(" ORDER BY sp_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Buy_Supply.class));
	}

	@Override
	@Transactional
	public void save(T_Buy_Supply supply) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(sp_code+0)) from t_buy_supply ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(supply), String.class);
		supply.setSp_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_supply");
		sql.append(" (sp_code,sp_name,sp_spell,sp_rate,sp_init_debt,sp_ar_code,sp_buy_cycle,sp_settle_cycle,companyid)");
		sql.append(" VALUES(:sp_code,:sp_name,:sp_spell,:sp_rate,:sp_init_debt,:sp_ar_code,:sp_buy_cycle,:sp_settle_cycle,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply),holder);
		int id = holder.getKey().intValue();
		supply.setSp_id(id);
		//插入数据到从表
		sql.setLength(0);
		sql.append("INSERT INTO t_buy_supply_info");
		sql.append(" (spi_sp_code,spi_man,spi_tel,spi_mobile,spi_addr,spi_bank_open,spi_bank_code,spi_remark,spi_earnest,spi_deposit,companyid)");
		sql.append(" VALUES(:sp_code,:spi_man,:spi_tel,:spi_mobile,:spi_addr,:spi_bank_open,:spi_bank_code,:spi_remark,:spi_earnest,:spi_deposit,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
	}

	@Override
	@Transactional
	public void del(Integer sp_id, String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_buy_supply");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sp_id", sp_id));
		sql.setLength(0);
		sql.append(" DELETE FROM t_buy_supply_info");
		sql.append(" WHERE spi_sp_code=:sp_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid));
	}

	@Override
	public T_Buy_Supply queryByID(Integer sp_id) {
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,spi_man,spi_tel,spi_mobile,sp_rate,sp_init_debt,sp_ar_code,sp_buy_cycle,");
		sql.append("sp_settle_cycle,spi_earnest,spi_deposit,spi_addr,spi_remark,spi_bank_open,spi_bank_code");
		sql.append(" FROM t_buy_supply supply ");
		sql.append(" INNER JOIN t_buy_supply_info supplyinfo");
		sql.append(" ON supply.sp_code = supplyinfo.spi_sp_code ");
		sql.append(" AND supply.companyid = supplyinfo.companyid ");
		sql.append(" WHERE sp_id=:sp_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("sp_id", sp_id),new BeanPropertyRowMapper<>(T_Buy_Supply.class));
		}catch(Exception e){
			return null;
		}
	}
	@Override
	public T_Buy_Supply load(String sp_code,Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,sp_name,spi_man,spi_tel,spi_mobile,sp_rate,sp_init_debt,sp_ar_code,sp_buy_cycle,");
		sql.append(" IFNULL(sp_payable,0) AS sp_payable,IFNULL(sp_payabled,0) AS sp_payabled,IFNULL(sp_prepay,0) AS sp_prepay,");
		sql.append("sp_settle_cycle,spi_earnest,spi_deposit,spi_addr,spi_remark,spi_bank_open,spi_bank_code");
		sql.append(" FROM t_buy_supply supply ");
		sql.append(" INNER JOIN t_buy_supply_info supplyinfo");
		sql.append(" ON supply.sp_code = supplyinfo.spi_sp_code ");
		sql.append(" AND supply.companyid = supplyinfo.companyid ");
		sql.append(" WHERE sp_code=:sp_code");
		sql.append(" AND supply.companyid = :companyid");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Supply.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	@Transactional
	public void update(T_Buy_Supply supply) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_buy_supply");
		sql.append(" SET sp_name=:sp_name");
		sql.append(" ,sp_spell=:sp_spell");
		sql.append(" ,sp_rate=:sp_rate");
		if(supply.getSp_init_debt() != null){
			sql.append(" ,sp_init_debt=:sp_init_debt");
		}
		sql.append(" ,sp_ar_code=:sp_ar_code");
		sql.append(" ,sp_buy_cycle=:sp_buy_cycle");
		sql.append(" ,sp_settle_cycle=:sp_settle_cycle");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
		sql.setLength(0);
		sql.append(" UPDATE t_buy_supply_info");
		sql.append(" SET spi_man=:spi_man");
		sql.append(" ,spi_tel=:spi_tel");
		sql.append(" ,spi_mobile=:spi_mobile");
		sql.append(" ,spi_addr=:spi_addr");
		sql.append(" ,spi_bank_open=:spi_bank_open");
		sql.append(" ,spi_bank_code=:spi_bank_code");
		sql.append(" ,spi_remark=:spi_remark");
		sql.append(" ,spi_earnest=:spi_earnest");
		sql.append(" ,spi_deposit=:spi_deposit");
		sql.append(" WHERE spi_sp_code=:sp_code");
		sql.append(" AND companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
	}

	@Override
	public T_Buy_Supply loadSupply(String sp_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT sp_id,sp_code,");
		sql.append(" IFNULL(sp_payable,0) AS sp_payable,IFNULL(sp_payabled,0) AS sp_payabled,IFNULL(sp_prepay,0) AS sp_prepay");
		sql.append(" FROM t_buy_supply t");
		sql.append(" WHERE sp_code = :sp_code");
		sql.append(" AND companyid = :companyid");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), 
					new MapSqlParameterSource().addValue("sp_code", sp_code).addValue("companyid", companyid),
					new BeanPropertyRowMapper<>(T_Buy_Supply.class));
		} catch (Exception e) {
			return null;
		}
	}
	
	@Override
	public void updatePayable(T_Buy_Supply supply) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_supply SET sp_payable=:sp_payable WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
	}
	
	@Override
	public void updatePrepay(T_Buy_Supply supply) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_supply SET sp_prepay=:sp_prepay WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
	}
	
	@Override
	public void updateSettle(T_Buy_Supply supply) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_buy_supply ");
		sql.append(" SET sp_payable=:sp_payable ");
		sql.append(" ,sp_payabled=:sp_payabled");
		sql.append(" ,sp_prepay=:sp_prepay");
		sql.append(" WHERE sp_id=:sp_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(supply));
	}
	
	private String getMoneyDetailsSQL(Map<String, Object> params,String type){
		if (StringUtil.isNotEmpty(params.get("type"))) {
			params.put("types", Arrays.asList(params.get("type").toString().split(",")));
		}
		if (StringUtil.isNotEmpty(params.get("pay_state"))) {
			params.put("pay_states", Arrays.asList(params.get("pay_state").toString().split(",")));
		}
		StringBuffer sql = new StringBuffer();
		if("enter".equals(type)){
			if(StringUtil.isNotEmpty(params.get("supply_code"))){
				sql.append(" AND et_supply_code = :supply_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND et_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND et_make_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND et_make_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(et_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND et_type IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND et_pay_state IN (:pay_states) ");
			}
			sql.append(" AND et_ar_state = 1");
		}else if("fee".equals(type)){
			if(StringUtil.isNotEmpty(params.get("supply_code"))){
				sql.append(" AND fe_supply_code = :supply_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND fe_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND fe_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND fe_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(fe_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				sql.append(" AND 3 IN (:types) ");
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				sql.append(" AND fe_pay_state IN (:pay_states) ");
			}
			sql.append(" AND fe_ar_state = 1");
		}else if("prepay".equals(type)){
			if(StringUtil.isNotEmpty(params.get("supply_code"))){
				sql.append(" AND pp_supply_code = :supply_code ");
			}
			if(StringUtil.isNotEmpty(params.get("manager"))){
				sql.append(" AND pp_manager = :manager ");
			}
			if(StringUtil.isNotEmpty(params.get("begindate"))){
				sql.append(" AND pp_date >= :begindate ");
			}
			if(StringUtil.isNotEmpty(params.get("enddate"))){
				sql.append(" AND pp_date <= :enddate ");
			}
			if (StringUtil.isNotEmpty(params.get("number"))) {
				sql.append(" AND INSTR(pp_number,:number) > 0 ");
			}
			if (StringUtil.isNotEmpty(params.get("type"))) {
				String _type = StringUtil.trimString(params.get("type"));
				if(_type.contains("4") && _type.contains("5")){
				}else if(_type.contains("4")){
					sql.append(" AND pp_money > 0");
				}else if(_type.contains("5")){
					sql.append(" AND pp_money < 0");
				}else{
	        		sql.append(" AND 1=2 ");
	        	}
			}
			if (StringUtil.isNotEmpty(params.get("pay_state"))) {
				String _pay_state = StringUtil.trimString(params.get("pay_state"));
				if(!_pay_state.contains("2")){
					sql.append(" AND 1=2 ");
				}
			}
			sql.append(" AND pp_ar_state = 1");
		}
		sql.append(" AND t.companyid = :companyid");
		return sql.toString();
	}

	@Override
	public Map<String, Object> countsumMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT");
		sql.append(" COUNT(1) AS totalCount,");
		sql.append(" SUM(money) AS money,");
		sql.append(" SUM(discount_money) AS discount_money,");
		sql.append(" SUM(prepay) AS prepay,");
		sql.append(" SUM(payabled) AS payabled,");
		sql.append(" SUM(payable) AS payable");
		sql.append(" FROM");
		sql.append(" (SELECT et_money AS money,et_discount_money AS discount_money,et_prepay AS prepay,et_payabled AS payabled,et_payable AS payable");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "enter"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_money AS money,fe_discount_money AS discount_money,fe_prepay AS prepay,fe_payabled AS payabled,fe_payable AS payable");
		sql.append(" FROM t_buy_fee t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL");
		sql.append(" SELECT 0 AS money,0 AS discount_money,0 AS prepay,pp_money AS payabled,0 AS payable");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp");
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public List<SupplyMoneyDetailsDto> listMoneyDetails(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT * FROM (");
		sql.append(" SELECT et_id AS id,et_make_date AS make_date,et_number AS number,et_type AS type,et_supply_code AS supply_code,et_pay_state AS pay_state,");
		sql.append(" et_money AS money,et_discount_money AS discount_money,et_prepay AS prepay,et_payabled AS payabled,et_payable AS payable,et_manager AS manager");
		sql.append(" ,(SELECT sp_name FROM t_buy_supply sp WHERE sp_code = et_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name");
		sql.append(" FROM t_buy_enter t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "enter"));
		sql.append(" UNION ALL");
		sql.append(" SELECT fe_id AS id,fe_date AS make_date,fe_number AS number,3 AS type,fe_supply_code AS supply_code,fe_pay_state AS pay_state,");
		sql.append(" fe_money AS money,fe_discount_money AS discount_money,fe_prepay AS prepay,fe_payabled AS payabled,fe_payable AS payable,fe_manager AS manager");
		sql.append(" ,(SELECT sp_name FROM t_buy_supply sp WHERE sp_code = fe_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name");
		sql.append(" FROM t_buy_fee t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "fee"));
		sql.append(" UNION ALL");
		sql.append(" SELECT pp_id AS id,pp_date AS make_date,pp_number AS number,IF(pp_money>0,4,5) AS type,pp_supply_code AS supply_code,2 AS pay_state,");
		sql.append(" 0 AS money,0 AS discount_money,0 AS prepay,pp_money AS payabled,0 AS payable,pp_manager AS manager");
		sql.append(" ,(SELECT sp_name FROM t_buy_supply sp WHERE sp_code = pp_supply_code AND sp.companyid = t.companyid LIMIT 1) AS supply_name");
		sql.append(" FROM t_buy_prepay t");
		sql.append(" WHERE 1=1");
		sql.append(getMoneyDetailsSQL(params, "prepay"));
		sql.append(" )temp ");
		if(StringUtil.isNotEmpty(params.get(CommonUtil.SIDX))){
			sql.append(" ORDER BY ").append(params.get(CommonUtil.SIDX)).append(" ").append(params.get(CommonUtil.SORD));
		}else{
			sql.append(" ORDER BY make_date DESC ");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(SupplyMoneyDetailsDto.class));
	}
	
	
	
}
